﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Arc.DairyMan.DairyMan.Model;

namespace Arc.DairyMan.DairyMan.DataAccess
{
    public abstract class AbsDal
    {
        public SqlConnection Connection { get; set; }
        public SqlTransaction DbTransaction { get; private set; }

        protected static string ConnectionString = @"Data Source=.\SQLEXPRESS;Integrated Security=True;";


        static string DBName = "DairyMan10";

        //public static string ConnectionString
        //{
        //    get
        //    {
        //        return ConfigurationManager.ConnectionStrings["LocalDBConnection"].ToString();
        //    }
        //}
            
        static SqlConnection OnceConn = new SqlConnection(ConnectionString);

        static AbsDal()
        {
            try
            {
                SqlCommand _cmd = OnceConn.CreateCommand();
                _cmd.CommandText = "use master";
                _cmd.CommandType = CommandType.Text;
                OnceConn.Open();
                _cmd.ExecuteNonQuery();
                OnceConn.Close();

                _cmd = OnceConn.CreateCommand();
                _cmd.CommandText = "select * from sys.databases where name = '" + DBName + @"'";
                _cmd.CommandType = CommandType.Text;
                DataTable _dt = new DataTable();
                SqlDataAdapter _da = new SqlDataAdapter(_cmd);
                _da.Fill(_dt);

                if (_dt.Rows.Count > 0)
                {
                    _cmd = OnceConn.CreateCommand();
                    _cmd.CommandText = "sp_detach_db";
                    _cmd.CommandType = CommandType.StoredProcedure;
                    _cmd.Parameters.Add(new SqlParameter("@dbname", DBName));
                    OnceConn.Open();
                    int _retval = _cmd.ExecuteNonQuery();
                    OnceConn.Close();
                }

                ConnectionString =
                    @"Data Source=.\SQLEXPRESS;
                            Integrated Security=True;
                            AttachDbFilename=|DataDirectory|" + DBName + @".mdf;
                            Initial Catalog=" + DBName;

                OnceConn.ConnectionString = ConnectionString;
                OnceConn.Open();
                OnceConn.Close();
            }
            catch (Exception ex)
            {

                throw;
            }
        }

        public AbsDal(SqlTransaction ParentTransaction = null)
        {
            DbTransaction = ParentTransaction;
            if (DbTransaction != null)
                Connection = ParentTransaction.Connection;
            else
                Connection = new SqlConnection(ConnectionString);
        }

        public void BeginTransaction(bool TransactionStarter)
        {
            if (!TransactionStarter) return;
            try
            {
                if (Connection == null)
                    Connection = new SqlConnection(ConnectionString);
                //else
                Connection.Open();
                DbTransaction = Connection.BeginTransaction();
            }
            catch
            { throw; }
        }

        public void CommitTransaction(bool TransactionStarter)
        {
            if (!TransactionStarter) return;
            try
            {
                DbTransaction.Commit();
                Connection.Close();
            }
            catch
            { throw; }
        }

        public void RollbackTransaction(bool TransactionStarter)
        {
            if (!TransactionStarter) return;
            try
            {
                DbTransaction.Rollback();
                Connection.Close();
            }
            catch
            { throw; }
        }

        public AbsModel ModelObj { get; set; }
        public abstract int Transaction();
        public abstract void Select(string Critiera, DataSet Ds);
        public abstract void Search(string Critiera, DataSet Ds);

        public void GetDataTable(string _SQL, DataSet Ds)
        {
            bool ConOpen = (Connection.State == ConnectionState.Open);
            try
            {
                SqlCommand _Cmd = Connection.CreateCommand();
                if (ConOpen)
                    _Cmd.Transaction = DbTransaction;
                else
                    Connection.Open();

                _Cmd.CommandText = _SQL;
                SqlDataAdapter _Da = new SqlDataAdapter(_Cmd);
                _Da.Fill(Ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (!ConOpen)
                    Connection.Close();
            }
        }

        public DataTable GetDataTable(string _SQL, string Table_Name, DataSet Ds)
        {
            bool ConOpen = (Connection.State == ConnectionState.Open);
            try
            {
                SqlCommand _Cmd = Connection.CreateCommand();
                if (ConOpen)
                    _Cmd.Transaction = DbTransaction;
                else
                    Connection.Open();
                _Cmd.CommandText = _SQL;
                SqlDataAdapter _Da = new SqlDataAdapter(_Cmd);
                DataTable _Dt = Ds.Tables[Table_Name];

                if (_Dt != null)
                    _Dt.Clear();
                else
                {
                    _Dt = new DataTable(Table_Name);
                    Ds.Tables.Add(_Dt);
                }
                _Da.Fill(_Dt);
                return _Dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (!ConOpen)
                    Connection.Close();
            }
        }

        protected int ExecuteCommand(SqlCommand Command)
        {
            Command.Transaction = DbTransaction;
            int _rowsAffected = Command.ExecuteNonQuery();
            return _rowsAffected;
        }


    }
}
